"""
数据库元数据服务
提供数据库表和列的元数据信息
"""

from typing import List, Dict, Any, Optional
from sqlalchemy.orm import Session
from sqlalchemy import Table, MetaData, inspect
from sqlalchemy.exc import SQLAlchemyError

from src.models.database_metadata import TableMetadata, ColumnMetadata, DatabaseMetadataResponse, ColumnType
from src.services.query_cache_service import query_cache_service
from src.utils.logging import get_logger

logger = get_logger(__name__)


class DatabaseMetadataService:
    """数据库元数据服务类"""
    
    @staticmethod
    def get_all_tables_metadata(db: Session) -> DatabaseMetadataResponse:
        """
        获取数据库中所有表的元数据信息
        
        Args:
            db: 数据库会话
            
        Returns:
            数据库元数据响应对象
        """
        try:
            # 使用SQLAlchemy的inspect获取表信息
            inspector = inspect(db.get_bind())
            table_names = inspector.get_table_names()
            
            tables = []
            for table_name in table_names:
                # 获取表的列信息
                columns = DatabaseMetadataService.get_table_columns(db, table_name)
                
                # 获取主键信息
                pk_info = inspector.get_pk_constraint(table_name)
                primary_keys = pk_info.get('constrained_columns', [])
                
                # 获取索引信息
                indexes = inspector.get_indexes(table_name)
                # 将索引信息转换为字典列表以匹配TableMetadata模型要求
                indexes_list = []
                for index in indexes:
                    indexes_list.append({
                        'name': index.get('name'),
                        'column_names': index.get('column_names'),
                        'unique': index.get('unique', False)
                    })
                
                # 构建表元数据对象
                table_info = TableMetadata(
                    name=table_name,
                    columns=columns,
                    primary_keys=primary_keys,
                    indexes=indexes_list,
                    schema_name=None,
                    comment=None,
                    row_count=None
                )
                tables.append(table_info)
            
            # 构建响应对象
            response = DatabaseMetadataResponse(
                tables=tables,
                total_tables=len(tables)
            )
            
            return response
            
        except SQLAlchemyError as e:
            logger.error(f"获取所有表元数据失败: {e}")
            raise
        except Exception as e:
            logger.error(f"获取所有表元数据时发生未知错误: {e}")
            raise
    
    @staticmethod
    def get_all_tables(db: Session) -> List[TableMetadata]:
        """
        获取数据库中所有表的信息
        
        Args:
            db: 数据库会话
            
        Returns:
            表信息列表
        """
        try:
            # 使用SQLAlchemy的inspect获取表信息
            inspector = inspect(db.get_bind())
            table_names = inspector.get_table_names()
            
            tables = []
            for table_name in table_names:
                table_info = TableMetadata(
                    name=table_name,
                    columns=[],
                    schema_name=None,
                    comment=None,
                    row_count=None
                )
                tables.append(table_info)
            
            return tables
            
        except SQLAlchemyError as e:
            logger.error(f"获取表信息失败: {e}")
            raise
        except Exception as e:
            logger.error(f"获取表信息时发生未知错误: {e}")
            raise
    
    @staticmethod
    def get_table_columns(db: Session, table_name: str) -> List[ColumnMetadata]:
        """
        获取指定表的列信息
        
        Args:
            db: 数据库会话
            table_name: 表名
            
        Returns:
            列信息列表
        """
        try:
            # 使用SQLAlchemy的inspect获取列信息
            inspector = inspect(db.get_bind())
            columns = inspector.get_columns(table_name)
            
            column_infos = []
            for column in columns:
                # 获取约束信息
                constraints = []
                if column.get('primary_key', False):
                    constraints.append("primary_key")
                if not column.get('nullable', True):
                    constraints.append("not_null")
                
                column_info = ColumnMetadata(
                    name=column['name'],
                    type=ColumnType.STRING,  # 使用枚举值而不是字符串
                    python_type=str(type(column['type'])).split("'")[1],
                    nullable=column.get('nullable', True),
                    default=str(column.get('default', '')) if column.get('default') is not None else None,
                    constraints=constraints,
                    is_primary=column.get('primary_key', False),
                    max_length=None,
                    foreign_key=None,
                    comment=None
                )
                column_infos.append(column_info)
            
            return column_infos
            
        except SQLAlchemyError as e:
            logger.error(f"获取表 {table_name} 的列信息失败: {e}")
            raise
        except Exception as e:
            logger.error(f"获取表 {table_name} 的列信息时发生未知错误: {e}")
            raise
    
    @staticmethod
    def get_table_info(db: Session, table_name: str) -> TableMetadata:
        """
        获取指定表的完整信息
        
        Args:
            db: 数据库会话
            table_name: 表名
            
        Returns:
            表信息
        """
        try:
            columns = DatabaseMetadataService.get_table_columns(db, table_name)
            
            table_info = TableMetadata(
                name=table_name,
                columns=columns,
                schema_name=None,
                comment=None,
                row_count=None
            )
            
            return table_info
            
        except Exception as e:
            logger.error(f"获取表 {table_name} 信息失败: {e}")
            raise
    
    @staticmethod
    def get_table_metadata(db: Session, table_name: str) -> Optional[TableMetadata]:
        """
        获取指定表的元数据信息
        
        Args:
            db: 数据库会话
            table_name: 表名
            
        Returns:
            表元数据对象或None（如果表不存在）
        """
        try:
            # 使用SQLAlchemy的inspect获取表信息
            inspector = inspect(db.get_bind())
            
            # 检查表是否存在
            table_names = inspector.get_table_names()
            if table_name not in table_names:
                return None
            
            # 获取表的列信息
            columns = DatabaseMetadataService.get_table_columns(db, table_name)
            
            # 获取主键信息
            pk_info = inspector.get_pk_constraint(table_name)
            primary_keys = pk_info.get('constrained_columns', [])
            
            # 获取索引信息
            indexes = inspector.get_indexes(table_name)
            # 将索引信息转换为字典列表以匹配TableMetadata模型要求
            indexes_list = []
            for index in indexes:
                indexes_list.append({
                    'name': index.get('name'),
                    'column_names': index.get('column_names'),
                    'unique': index.get('unique', False)
                })
            
            # 构建表元数据对象
            table_info = TableMetadata(
                name=table_name,
                columns=columns,
                primary_keys=primary_keys,
                indexes=indexes_list,
                schema_name=None,
                comment=None,
                row_count=None
            )
            
            return table_info
            
        except SQLAlchemyError as e:
            logger.error(f"获取表 {table_name} 元数据失败: {e}")
            raise
        except Exception as e:
            logger.error(f"获取表 {table_name} 元数据时发生未知错误: {e}")
            raise
    
    @staticmethod
    def refresh_table_metadata(db: Session, table_name: Optional[str] = None):
        """
        刷新表元数据（清理相关缓存）
        
        Args:
            db: 数据库会话
            table_name: 特定表名，如果为None则刷新所有表的元数据
        """
        try:
            if table_name:
                # 清理特定表的查询缓存
                query_cache_service.invalidate_table_cache(table_name)
                logger.info(f"已刷新表 {table_name} 的元数据缓存")
            else:
                # 清理所有查询缓存
                query_cache_service.invalidate_table_cache("")
                logger.info("已刷新所有表的元数据缓存")
                
        except Exception as e:
            logger.error(f"刷新表元数据缓存失败: {e}")
            raise